import pandas as pd
import numpy as np
from pathlib import Path

# 1. 配置文件路径
file_sigmoid = "Media_4Metrics_SigmoidDeviation.xlsx"
file_js      = "JSD_Quad&Event.xlsx"
output_file  = "Media_6Metrics_DeviationSummary.xlsx"

# 2. 读取四个 Sigmoid 映射指标
df_sig = pd.read_excel(file_sigmoid, index_col="Media", engine="openpyxl")

# 3. 读取两个 JSD 指标
df_jsd = pd.read_excel(file_js, index_col="Media", engine="openpyxl")

# 4. 挑选并重命名六个指标列
sigmoid_cols = [
    "SigmoidDeviation_BalanceIndex",
    "SigmoidDeviation_ImpactIndex",
    "SigmoidDeviation_ToneIndex",
    "SigmoidDeviation_EventCount"
]
jsd_cols = ["JS_Quad", "JS_Event"]

# 5. 合并两个表，只保留在两者中都出现的媒体
df = df_sig[sigmoid_cols].join(df_jsd[jsd_cols], how="inner")

# 6. 计算几何均值和算术均值
epsilon = 1e-12  # 防止 0 导致几何均值计算问题
values = df[sigmoid_cols + jsd_cols].clip(lower=epsilon)

# 几何均值：exp(mean(log(x_i)))
df["GeometricMean"] = np.exp(np.log(values).mean(axis=1))

# 算术均值：mean(x_i)
df["ArithmeticMean"] = values.mean(axis=1)

# 7. 保存结果
df.to_excel(output_file)

print(f"✅ 结果已保存至 {output_file}")
